MySQL 存储过程与定时任务

场景
存储过程
- 存储过程的操作
  - 创建存储过程
  - 调用存储过程
游标
查看定时器是否开启

场景

定时执行某些任务, 如每天的某时刻定时删除某些数据.

一般会编写存储过程, 设置删除条件, 然后使用定时任务运行存储过程.

存储过程

在 MySQL 5 以后, 开始支持存储过程.

存储过程常使用在需要一次性执行多条语句, 编译后放在数据库中, 指定存储过程的名字及相应的参数, 就可以调用它.

其实这相当于平时编程时用到的函数, 通过多条语句完成一定的功能. 所以其实可以读取数据库, 在函数中完成相关的逻辑. 但是数据库性能极高, 如果能在数据库中处理好再返回, 可以提高程序的整体性能.

存储过程也存在一些缺点:

存储过程比较复杂, 不容易编写;
可能没有创建存储过程的安全访问权限. 许多 DBA 会限制一般用户创建存储过程, 但允许使用.

存储过程的操作

创建存储过程

获得订单统计, 加入营业税, 返回统计:

DELIMITER //

CREATE PROCEDURE ordertotal(IN onumber INT, IN taxable BOOLEAN, OUT ototal DECIMAL(8,2)) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
    DECLARE total DECIMAL(8,2);  -- 声明 total 变量
    DECLARE taxrate INT DEFAULT 6;  -- 声明 taxrate 变量
    SELECT Sum(item_price*quantity) FROM orderitems WHERE order_num=onumber INTO total;
    IF taxable THEN  -- 如果需要计算税率
        SELECT total+(total/100*taxrate) INTO total;
    END IF;
    SELECT total into ototal;
END //

DELIMITER;

DELIMITER / 告诉命令行, 程序使用 / 作为新的语句结束分隔符, 这样可以将存储过程中的分号正确解释, 否则 MySQL 会将分号作为程序结束的标志.

OUT 表示输出, IN 表示输入, INOUT 表示既作为输入也作为输出, INTO 表示将结果存储到变量.

DECLARE 表示定义局部变量, 需要指明变量名和数据类型.

COMMENT 不是必需的, 但如果给出了, 将会在 SHOW PROCEDURE STATUS 的结果中显示.

调用存储过程

CALL ordertotal(20005, 1, @total);

select @total

游标

游标的使用场景: MySQL 检索操作返回的一组称为结果集的行, 有时我们需要遍历这个结果集, 以便于进一步处理. 在 MySQL 中, 游标只能用于存储过程和函数.

创建游标

-- 定义游标, 查询商家加入了哪些联盟                                                                                                             
declare cursor_alliance_name cursor for select alliance_name from member where member_name=in_shop_name;

打开和关闭游标

open cursor_alliance_name;
close cursor_alliance_name;

已声明过的游标, 如果关闭后还想再使用, 不需要再次声明, 只要 open 即可.

使用游标数据

使用 fetch 语句分别访问检索的数据的每一行, fetch 指定检索什么数据(所需的列), 它还会向前移动游标中的内部行指针, 使下一条 fetch 语句检索下一行(不重复读取同一行).

delimiter //
create procedure query_alliance(IN in_shop_name varchar(50)) comment '查询商家加入的联盟, 获取联盟的活动的设置信息并返回'
BEGIN
    declare done boolean default false;
    declare o varchar(50);

    -- 定义游标, 查询商家加入了哪些联盟
    declare cur cursor for select alliance_name from member where member_name=in_shop_name;
    declare continue HANDLER for not found set done = true;

    -- 每次都先清空 query_activity 表, 再将检索结果放入 query_activity 表中
    set @clear_table = concat("truncate table query_activity");
    prepare ct from @clear_table;
    execute ct;

    -- 打开游标
    open cur;
    repeat
        fetch cur into o;
        insert into query_activity(alliance_name, activity, beginTime, endTime, totalCoupons, realCoupons, energy) (select * from alliance_activity where alliance_name=o group by alliance_name);
    until done end repeat;
    close cur;

    select * from query_activity;
END //
delimiter ;

sqlstate '02000' 表示一个未找到的条件, 当 repeat 由于没有更多的行供循环而不能继续时, 会出现这个条件.

注意: 变量的声明必须放在游标的声明的前面.

查看定时器是否开启

SHOW VARIABLES LIKE 'event_scheduler';  // 查看定时器状态
SET GLOBAL event_scheduler = 1;         // 开启事件设置

Pinvon's Blog